﻿'Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Data.SqlClient
Imports System.Net
Imports System.Xml

Module DS2

    'Private xlApp1 As Excel.Application 'XLS Fuente
    'Private xlWorkBook1 As Excel.Workbook
    'Private xlWorkSheet1 As Excel.Worksheet

    'Private xlApp2 As Excel.Application 'XLS template
    'Private xlWorkBook2 As Excel.Workbook
    'Private xlWorkSheet2 As Excel.Worksheet

    Private dirExport As String
    Private dirLog As String
    Private FTPTimeOut As Integer
    Private dirInstall As String
    Private archXml2 As String 'Archivo Template
    Private dirFTP As String
    Private conf As Boolean

    Private imper As Boolean

    Private dirXLS As String
    Private usrXLS As String
    Private passXLS As String
    Private dirTemp As String

    Private xmlDoc2 As New XmlDocument

    Sub inicializar()

        Try
            'log("inicializar")

            Dim xmldoc As New XmlDocument()
            Dim a As String
            Dim b As Integer


            Try
                dirInstall = My.Computer.Registry.GetValue("HKEY_LOCAL_MACHINE\Software\Anew\AppQfinitiExport", "dirInstall", Nothing).ToString.Trim
            Catch ex As Exception
                dirInstall = My.Computer.Registry.GetValue("HKEY_LOCAL_MACHINE\Software\Wow6432Node\Anew\AppQfinitiExport", "dirInstall", Nothing).ToString.Trim
            End Try


            xmldoc.Load(dirInstall & "ConfigAppExport.xml")


            a = xmldoc.SelectSingleNode("/CONFIG/INFODATA/PATH").InnerXml
            b = a.Length
            If InStr(a, "\\") = 0 Then
                If InStr(b - 1, a, "\") = 0 Then
                    dirXLS = a.Insert(b, "\")
                Else
                    dirXLS = a
                End If
                usrXLS = ""
                passXLS = ""
                imper = False
            Else
                dirXLS = a
                usrXLS = xmldoc.SelectSingleNode("/CONFIG/INFODATA/USER").InnerXml
                passXLS = xmldoc.SelectSingleNode("/CONFIG/INFODATA/PASS").InnerXml
                imper = True
            End If

            a = xmldoc.SelectSingleNode("/CONFIG/INFOLOG/DIRLOG").InnerXml
            b = a.Length
            If a = "" Then
                dirLog = dirInstall
            Else
                If InStr(b - 1, a, "\") = 0 Then
                    dirLog = a.Insert(b, "\")
                Else
                    dirLog = a
                End If
            End If


        Catch ex As Exception
            Console.WriteLine("Hello world")
            'log(ex.Message)
            dirExport = "C:\ToExport\"
            dirLog = dirInstall & "Logs"

        End Try


    End Sub

    Sub Main()

        inicializar()
        archXml2 = dirInstall & "Data.xml"
        dirFTP = dirInstall & "FTP\"
        dirTemp = dirInstall & "tmp\"

        Try
            My.Computer.FileSystem.CopyFile(dirInstall & "Template.xml", archXml2, True)

            If imper = True Then
                Try
                    Dim impersonator As New AliasAccount(usrXLS, passXLS)
                    impersonator.BeginImpersonation()
                    Dim dir As DirectoryInfo = New DirectoryInfo(dirXLS)
                    If dir.Exists Then
                        For Each fi As FileInfo In dir.GetFiles("*.xls", SearchOption.TopDirectoryOnly)
                            'log("Archivo Excel encontrado: " & fi.DirectoryName & "\" & fi.Name)
                            fi.CopyTo(dirTemp & fi.Name, True)
                            'XLSCreate(dirTemp & fi.Name)
                            conf = True
                            Try
                                fi.Delete()
                                'log("Archivo " & fi.DirectoryName & "\" & fi.Name & " borrado")
                            Catch ex As Exception
                                'log("No se pudo borrar el archivo: " & fi.DirectoryName & "\" & fi.Name)
                                'log(ex.Message)
                            End Try
                        Next
                    Else
                        Console.WriteLine("Ruta especificada o credenciales no validas")
                    End If

                    impersonator.EndImpersonation()
                    impersonator = Nothing
                    'releaseObject(impersonator)

                Catch ex As Exception
                    'log(ex.Message)
                    Console.WriteLine("Error Rutina: Main during impersonation")
                End Try
            Else
                Try

                    For Each foundFile As String In My.Computer.FileSystem.GetFiles(dirXLS, FileIO.SearchOption.SearchTopLevelOnly, "*.xls")
                        'log("Archivo Excel encontrado: " & foundFile)
                        'XLSCreate(foundFile)
                        conf = True
                    Next

                Catch ex As Exception
                    'log(ex.Message)
                    'log("Error Rutina: Main during local search")
                    Exit Sub
                End Try
            End If

            If conf = True Then
                My.Computer.FileSystem.MoveFile(archXml2, dirFTP & "Data.xml", True)
                'log("Archivo Excel transferido para FTP")
            End If

        Catch ex As Exception
            'log(ex.Message)
            'log("Error en la rutina Main")
            Exit Sub
        End Try
    End Sub


    'Sub XLSCreate(ByVal archXls As String)
    '    log("rutina:XLSCreate")

    '    Dim x As String = ""
    '    Dim fileWAV As String = ""
    '    Dim b As Integer = 0
    '    Dim c As String = ""
    '    Dim n As Integer


    '    Dim colTransID As Integer
    '    Dim colAgent As Integer
    '    Dim colDate As Integer
    '    Dim colTime As Integer
    '    Dim colDur As Integer
    '    Dim colANI As Integer
    '    Dim colDNIS As Integer
    '    Dim colConnid As Integer
    '    Dim colDir As Integer

    '    Try
    '        'xlApp1 = New Excel.ApplicationClass
    '        'xlWorkBook1 = xlApp1.Workbooks.Open(archXls)
    '        'xlWorkSheet1 = xlWorkBook1.Worksheets(1)

    '        n = 1

    '        Do Until b = 2
    '            'c = xlWorkSheet1.Cells(1, n).value
    '            If c = "" Then
    '                b = b + 1
    '                n = n + 1
    '            Else

    '                Select Case xlWorkSheet1.Cells(1, n).value
    '                    Case "Transaction ID", "Id de transacción"
    '                        colTransID = n
    '                    Case "Agent", "Agente"
    '                        colAgent = n
    '                    Case "Date", "Fecha"
    '                        colDate = n
    '                    Case "Time", "Hora"
    '                        colTime = n
    '                    Case "Duration (sec)", "Duración (seg)"
    '                        colDur = n
    '                    Case "DNIS"
    '                        colDNIS = n
    '                    Case "ANI"
    '                        colANI = n
    '                    Case "Connection ID", "Id. de conexión"
    '                        colConnid = n
    '                    Case "Call Direction", "Dirección de la Llamada"
    '                        colDir = n
    '                End Select
    '                n = n + 1

    '            End If

    '        Loop

    '        log("Columnas identificadas TransID: " & colTransID & " Agent: " & colAgent & " Date: " & colDate & " Time: " & colTime & " Duration: " & colDur & " DNIS: " & colDNIS & " ANI: " & colANI & " ConnID: " & colConnid & " Call Direction: " & colDir)




    '        xmlDoc2.Load(archXml2)





    '        xlApp2 = New Excel.ApplicationClass
    '        xlWorkBook2 = xlApp2.Workbooks.Open(archXml2)
    '        xlWorkSheet2 = xlWorkBook2.Worksheets(1)

    '        n = 2
    '        b = 0
    '        c = ""

    '        Do Until b = 3
    '            c = xlWorkSheet1.Cells(n, 1).value
    '            If c = "" Then
    '                b = b + 1
    '                n = n + 1
    '            Else
    '                fileWAV = NameFileWav(xlWorkSheet1.Cells(n, colDate).value, xlWorkSheet1.Cells(n, colTime).value)
    '                If fileWAV <> "" Then
    '                    xlWorkSheet2.Cells(n, 1).value = xlWorkSheet1.Cells(n, colTransID).value
    '                    xlWorkSheet2.Cells(n, 2).value = xlWorkSheet1.Cells(n, colAgent).value
    '                    xlWorkSheet2.Cells(n, 3).value = "'" & xlWorkSheet1.Cells(n, colDate).value
    '                    xlWorkSheet2.Cells(n, 4).value = "'" & xlWorkSheet1.Cells(n, colTime).value
    '                    xlWorkSheet2.Cells(n, 5).value = "'" & Duracion(xlWorkSheet1.Cells(n, colDur).value)
    '                    xlWorkSheet2.Cells(n, 6).value = xlWorkSheet1.Cells(n, colDNIS).value
    '                    xlWorkSheet2.Cells(n, 7).value = xlWorkSheet1.Cells(n, colANI).value
    '                    xlWorkSheet2.Cells(n, 8).value = "OK"
    '                    xlWorkSheet2.Cells(n, 9).value = xlWorkSheet1.Cells(n, colConnid).value
    '                    xlWorkSheet2.Cells(n, 10).value = xlWorkSheet1.Cells(n, colDir).value
    '                    xlWorkSheet2.Cells(n, 11).value = fileWAV
    '                    My.Computer.FileSystem.MoveFile(dirExport & fileWAV, dirFTP & fileWAV, True)
    '                End If
    '                n = n + 1

    '            End If
    '        Loop

    '        xlWorkBook1.Close()
    '        xlApp1.Quit()

    '        xlWorkBook2.Save()
    '        xlWorkBook2.Close()
    '        xlApp2.Quit()

    '        xlWorkSheet1 = Nothing
    '        xlWorkBook1 = Nothing
    '        xlApp1 = Nothing

    '        releaseObject(xlWorkSheet1)
    '        releaseObject(xlWorkBook1)
    '        releaseObject(xlApp1)

    '        xlWorkSheet2 = Nothing
    '        xlWorkBook2 = Nothing
    '        xlApp2 = Nothing

    '        releaseObject(xlWorkSheet2)
    '        releaseObject(xlWorkBook2)
    '        releaseObject(xlApp2)


    '        For Each foundFile As String In My.Computer.FileSystem.GetFiles(dirExport, FileIO.SearchOption.SearchTopLevelOnly, archXls.Replace(dirExport, ""))
    '            Try
    '                My.Computer.FileSystem.DeleteFile(foundFile, FileIO.UIOption.OnlyErrorDialogs, FileIO.RecycleOption.DeletePermanently)
    '            Catch ex As Exception
    '                log("Archivo borrado " & foundFile)
    '                log(ex.Message)
    '            End Try
    '        Next




    '    Catch ex As Exception

    '        log(ex.Message)
    '        Exit Sub

    '    End Try

    'End Sub

    'Public Function NameFileWav(ByVal fecha As String, ByVal tiempo As String) As String
    '    Dim name As String
    '    Dim result As String = ""


    '    Dim year As String
    '    Dim month As String
    '    Dim day As String

    '    year = fecha.Remove(4)
    '    month = fecha.Remove(0, 5)
    '    month = month.Remove(2)
    '    day = fecha.Remove(0, 8)
    '    fecha = month & "_" & day & "_" & year

    '    tiempo = tiempo.Replace(":", ";")
    '    tiempo = tiempo.Replace(".000", "")
    '    tiempo = tiempo.Remove(7)
    '    name = "*" & fecha & "_" & tiempo & "*.wav"

    '    For Each foundFile As String In My.Computer.FileSystem.GetFiles(dirExport, FileIO.SearchOption.SearchTopLevelOnly, name)
    '        result = foundFile
    '        log("Archivo WAV encontrado " & foundFile)
    '        Exit For
    '    Next
    '    NameFileWav = result.Replace(dirExport, "")

    'End Function

    'Function Duracion(ByVal dur As String) As String
    '    Dim stMin As String
    '    Dim stSeg As String
    '    Dim intMin As Integer
    '    Dim intSeg As Integer
    '    Dim dur2 As String
    '    Dim pos As Integer

    '    dur2 = dur.Trim
    '    '10:20
    '    pos = InStr(dur2, ":")

    '    stSeg = dur2.Remove(0, pos)
    '    stMin = dur2.Remove(pos - 1, 3)

    '    If stMin = "" Then
    '        intMin = 0
    '    Else
    '        intMin = System.Convert.ToInt16(stMin)
    '    End If

    '    intSeg = System.Convert.ToInt16(stSeg)

    '    intMin = intMin * 60
    '    intSeg = intSeg + intMin
    '    Duracion = System.Convert.ToString(intSeg)
    'End Function

    'Function FechayHora() As String
    '    FechayHora = Now.Date.ToString.Remove(InStr(Now.Date.ToString, " ")) & Now.TimeOfDay.ToString
    'End Function

    'Private Sub releaseObject(ByVal obj As Object)
    '    Try
    '        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    '        obj = Nothing
    '    Catch ex As Exception
    '        obj = Nothing
    '    Finally
    '        GC.Collect()
    '    End Try
    'End Sub



    'Sub log(ByVal mensaje As String)
    '    Try
    '        Dim oSW As New StreamWriter(dirInstall & "AppQfinitiExport.log", True)
    '        oSW.WriteLine(FechayHora & " " & mensaje)
    '        oSW.Flush()
    '        oSW.Close()
    '        releaseObject(oSW)
    '    Catch ex As Exception
    '        Console.WriteLine(ex.Message)
    '    End Try
    'End Sub

End Module
